﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using BIS.Portal.Modules.CMS.Entity;
using BIS.Utility.DAL;
using BIS.Utility.DAL.DataMapper;

namespace BIS.Portal.Modules.CMS.DAL
{
    public class AdminCmsCategoryDAL
    {
        /// <summary>
        /// Gets the by language in use id.
        /// </summary>
        /// <param name="languageInUseId">The language in use id.</param>
        /// <returns></returns>
        public static IList<AdminCmsCategoryEntity> GetByLanguageInUseId(int languageInUseId)
        {
            IList<AdminCmsCategoryEntity> list;

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_CATEGORY_GetByLanguageInUseId",
                                        new SqlParameter("@languageInUseId", languageInUseId)))
            {
                list = EntityMapper.FillCollection<AdminCmsCategoryEntity>(reader);
            }

            return list;
        }

        /// <summary>
        /// Gets the by language in use id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public static AdminCmsCategoryEntity GetByCateId(int categoryId, int languageUserId)
        {

            AdminCmsCategoryEntity objCate = null;

            SqlParameter[] _param = new SqlParameter[]
                                      {
                                          new SqlParameter("@category_id",categoryId),
                                         new SqlParameter("@language_in_use_id", languageUserId)
                                         
                                        };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_CATEGORY_GetByCateId", _param
                                        ))
            {
                objCate = EntityMapper.FillObject<AdminCmsCategoryEntity>(reader);
            }

            return objCate;
        }

        /// <summary>
        /// Adds the category.
        /// </summary>
        /// <param name="cmsCateEntity">The CMS cate entity.</param>
        /// <returns></returns>
        public static int AddCategory(AdminCmsCategoryEntity cmsCateEntity)
        {
            SqlParameter paraCateId = new SqlParameter("@category_id", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output

            };

            SqlParameter[] param = new SqlParameter[]
                                       {
                                           new SqlParameter("@parent_category_id", cmsCateEntity.Parent_category_id),
                                           new SqlParameter("@category_page_alias",cmsCateEntity.Category_page_alias),
                                           new SqlParameter("@article_page_alias", cmsCateEntity.Article_page_alias),
                                           new SqlParameter("@category_name", cmsCateEntity.Category_name),
                                           new SqlParameter("@category_type", cmsCateEntity.Category_type),
                                           new SqlParameter("@priority", cmsCateEntity.Priority),
                                           new SqlParameter("@show_in_menu", cmsCateEntity.Show_in_menu),
                                           new SqlParameter("@use_direct_url", cmsCateEntity.Use_direct_url),
                                           new SqlParameter("@last_update", cmsCateEntity.Last_update),
                                           new SqlParameter("@last_update_by", cmsCateEntity.Last_update_by),
                                           new SqlParameter("@status", cmsCateEntity.Status),
                                           new SqlParameter("@language_in_use_id", cmsCateEntity.Language_in_use_id),
                                           new SqlParameter("@category_description", cmsCateEntity.Category_description),
                                           new SqlParameter("@category_meta_title", cmsCateEntity.Category_meta_title),
                                           new SqlParameter("@category_meta_keyword", cmsCateEntity.Category_meta_keyword),
                                           new SqlParameter("@category_meta_description", cmsCateEntity.Category_meta_description),
                                           new SqlParameter("@category_direct_url", cmsCateEntity.Category_direct_url)
                                       };

            if (cmsCateEntity.Parent_category_id == 0)
            {
                param[0].Value = DBNull.Value;

            }

            int i = SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                              CommandType.StoredProcedure,
                                              "BIS_sCMS_CATEGORY_AddCategory",
                                              param);

            return i;
        }

        /// <summary>
        /// Updates the category.
        /// </summary>
        /// <param name="cmsCateEntity">The CMS cate entity.</param>
        /// <returns></returns>
        public static int UpdateCategory(AdminCmsCategoryEntity cmsCateEntity)
        {
            SqlParameter[] param = new SqlParameter[]
                                       {
                                           new SqlParameter("@category_id",cmsCateEntity.Category_id), 
                                           new SqlParameter("@parent_category_id", cmsCateEntity.Parent_category_id),
                                           new SqlParameter("@category_page_alias",cmsCateEntity.Category_page_alias),
                                           new SqlParameter("@article_page_alias", cmsCateEntity.Article_page_alias),
                                           new SqlParameter("@category_name", cmsCateEntity.Category_name),
                                           new SqlParameter("@category_type", cmsCateEntity.Category_type),
                                           new SqlParameter("@priority", cmsCateEntity.Priority),
                                           new SqlParameter("@show_in_menu", cmsCateEntity.Show_in_menu),
                                           new SqlParameter("@use_direct_url", cmsCateEntity.Use_direct_url),
                                           new SqlParameter("@last_update", cmsCateEntity.Last_update),
                                           new SqlParameter("@last_update_by", cmsCateEntity.Last_update_by),
                                           new SqlParameter("@status", cmsCateEntity.Status),
                                           new SqlParameter("@language_in_use_id", cmsCateEntity.Language_in_use_id),
                                           new SqlParameter("@category_description", cmsCateEntity.Category_description),
                                           new SqlParameter("@category_meta_title", cmsCateEntity.Category_meta_title),
                                           new SqlParameter("@category_meta_keyword", cmsCateEntity.Category_meta_keyword),
                                           new SqlParameter("@category_meta_description", cmsCateEntity.Category_meta_description),
                                           new SqlParameter("@category_direct_url", cmsCateEntity.Category_direct_url)
                                       };

            if (cmsCateEntity.Parent_category_id == 0)
            {
                param[1].Value = DBNull.Value;

            }

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                             CommandType.StoredProcedure,
                                             "BIS_sCMS_CATEGORY_UpdateCategory",
                                             param);
        }

        /// <summary>
        /// Deletes the category.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public static int DeleteCategory(int categoryId)
        {
            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                             CommandType.StoredProcedure,
                                             "BIS_sCMS_CATEGORY_DeleteCategory",
                                             new SqlParameter("@category_id", categoryId));
        }
        
        /// <summary>
        /// Determines whether this instance [can delete category] the specified category id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <param name="numOfChild">The num of child.</param>
        /// <returns></returns>
        public static void CheckParentForDeleteCate(int categoryId,ref int numOfChild)
        {
            SqlParameter[] _param = new SqlParameter[]
                                         {
                                             new SqlParameter("@category_id",categoryId),
                                             new SqlParameter("@numberofchild", SqlDbType.Int)
                                              {
                                                  Direction = ParameterDirection.Output
                                              }
                                         };

            SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                               CommandType.StoredProcedure,
                                               "BIS_sCMS_CATEGORY_CheckParentForDeleteCate",
                                               _param);


            numOfChild = (int) _param[1].Value;
        }

        /// <summary>
        /// Updates the status.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <param name="showInMenu">The show in menu.</param>
        /// <param name="useDirectLink">The use direct link.</param>
        /// <param name="status">The status.</param>
        /// <returns></returns>
        public static int UpdateStatus(int categoryId, int showInMenu, int useDirectLink, int status, string user, DateTime updateTime)
        {

            SqlParameter[] sqlParas = new SqlParameter[]
                                         {
                                             new SqlParameter("@category_id",categoryId),
                                             new SqlParameter("@use_direct_url",useDirectLink),
                                             new SqlParameter("@show_in_menu",showInMenu),
                                             new SqlParameter("@status",status),
                                              new SqlParameter("@last_update",updateTime),
                                             new SqlParameter("@last_update_by",user)
                                         };

            return SqlHelper.ExecuteNonQuery(DALConnection.ConnectionString,
                                               CommandType.StoredProcedure,
                                               "BIS_sCMS_CATEGORY_UpdateStatus",
                                               sqlParas);
        }

        /// <summary>
        /// Lấy category theo parent id
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iParent_category_id"></param>
        /// <returns></returns>
        public static IList<AdminCmsCategoryEntity> GetCategory(int iLanguage_in_use_id, int iParent_category_id)
        {
            IList<AdminCmsCategoryEntity> list;

            SqlParameter[] _param = new SqlParameter[]
                                      {
                                         new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                          new SqlParameter("@parent_category_id",iParent_category_id) 
                                        };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_CATEGORY_GetCategory", _param
                                        ))
            {
                list = EntityMapper.FillCollection<AdminCmsCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// lấy category cho cate parent id
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <returns></returns>
        public static IList<AdminCmsCategoryNameEntity> GetCategoryNameForParentCateId(int iLanguage_in_use_id)
        {
            IList<AdminCmsCategoryNameEntity> list;

            SqlParameter[] _param = new SqlParameter[]
                                      {
                                         new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                         
                                        };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_CATEGORY_GetCategoryNameForParentCateId", _param
                                        ))
            {
                list = EntityMapper.FillCollection<AdminCmsCategoryNameEntity>(reader);
            }

            return list;
        }
    }
}
